﻿IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_Report_TongHopThuChiTheoNgay]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_Report_TongHopThuChiTheoNgay]
GO


CREATE PROC [sp_Report_TongHopThuChiTheoNgay]
(
	@Ma_chi_nhanh 		INT,
	@Ngay_xem 			DATETIME
)	
AS
BEGIN
	/* 	
	* declare bien @Dau_thang de lay day du ngay dau tien cua thang,	
	* declare bien @Tong_tien_toan_chi_nhanh de luu tong tien cua toan chi nhanh trong thang do, SUM() cot Tong_tien cua bang PhieuNhapXuat,
	* declare bien @Thuc_thu_toan_chi_nhanh de luu tong tien khach thanh toan cua toan chi nhanh trong thang do, SUM() cot Thanh_toan cua bang PhieuNhapXuat,
	* tat ca dua vao ma chi nhanh va ngay xem
	*/
	DECLARE @Thu				NVARCHAR(50)
	DECLARE @Chi				NVARCHAR(50)	 
	DECLARE @Dau_thang			DATETIME
	DECLARE @Tong_tien_thu		INT
	DECLARE @Tong_tien_chi		INT
	DECLARE @Ngay_xem_in		NVARCHAR(10)	

	SET @Thu = N'A - Phần thu'
	SET @Chi = N'B - Phần chi'
	SET @Dau_thang = DATEADD(dd,-(DAY(DATEADD(mm,1,@Ngay_xem))-1),DATEADD(mm,0,@Ngay_xem))
	SET @Ngay_xem_in = CONVERT(NVARCHAR, DAY(@Ngay_xem)) + '/' + CONVERT(NVARCHAR, MONTH(@Ngay_xem)) + '/' + CONVERT(NVARCHAR, YEAR(@Ngay_xem))

	SET @Tong_tien_thu = (SELECT SUM(CASE 
										WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem 
										THEN B.Thanh_tien 
										ELSE 0 
									END)
			FROM ThanhVien AS A INNER JOIN PhieuThuChi AS B ON A.id = B.Ma_nhan_vien	
				INNER JOIN ThuChi AS C ON B.Ma_thu_chi = C.id
			WHERE A.Ma_chi_nhanh = @Ma_chi_nhanh AND C.Ma_loai_thu_chi = 0)	

	SET @Tong_tien_chi = (SELECT SUM(CASE 
										WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem 
										THEN B.Thanh_tien 
										ELSE 0 
									END)
			FROM ThanhVien AS A INNER JOIN PhieuThuChi AS B ON A.id = B.Ma_nhan_vien	
				INNER JOIN ThuChi AS C ON B.Ma_thu_chi = C.id
			WHERE A.Ma_chi_nhanh = @Ma_chi_nhanh AND C.Ma_loai_thu_chi = 1)
	
	/*
	* lay ten cua loai thu chi
	* tinh tong tien trong ngay moi viec thu hoac chi,
	* luy ke = tinh tong tien trong thang co ngay do ma nhan vien ban duoc
	* ti le = tong so tien cua moi loai thu chi * 100 / tong so tien cua toan bo cac loai thu chi trong ca chi nhanh (tat ca tinh trong thang)
	* dua vao ngay xem va chi nhanh
	*/	
	/* 
	* neu @Tong_tien_thu hoac @Tong_tien_chi = 0 tuc la khong co thu chi nen tat ca cac cot duoc set = 0 
	*/	
	BEGIN
		IF @Tong_tien_thu = 0 OR @Tong_tien_chi = 0
		BEGIN
			SELECT A.Ten AS 'Ten_thu_chi', 
				'Tong_tien_trong_ngay' = 0, 
				'Tong_tien_trong_thang' = 0, 
				'Ti_le' = 0,
				'Phan_loai' = @Thu,
				'Ngay_xem' = @Ngay_xem_in 
			FROM ThuChi AS A
			WHERE A.Ma_loai_thu_chi = 0
			UNION ALL
			SELECT A.Ten, 
				0, 
				0, 
				0,
				@Chi,
				@Ngay_xem_in 
			FROM ThuChi AS A
			WHERE A.Ma_loai_thu_chi = 1
		END

		ELSE
		BEGIN
			SELECT A.Ten AS 'Ten_thu_chi',
				SUM(CASE 
						WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) = @Ngay_xem
						THEN B.Thanh_tien 
						ELSE 0 
					END) AS 'Tong_tien_trong_ngay', 
				SUM(CASE 
						WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem 
						THEN B.Thanh_tien 
						ELSE 0 
					END) AS 'Tong_tien_trong_thang',
				ROUND((SUM(CASE 
								WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem 
								THEN B.Thanh_tien 
								ELSE 0 
							END)*100/@Tong_tien_thu), 2) AS 'Ti_le',
				'Phan_loai' = @Thu,
				'Ngay_xem' = @Ngay_xem_in
			FROM ThuChi AS A LEFT OUTER JOIN PhieuThuChi AS B ON A.id = B.Ma_thu_chi
				LEFT OUTER JOIN ThanhVien AS C ON B.Ma_nhan_vien = C.id
			WHERE C.Ma_chi_nhanh = @Ma_chi_nhanh
				AND A.Ma_loai_thu_chi = 0
			GROUP BY A.Ten
			UNION ALL
			SELECT A.Ten AS 'Ten_thu_chi',
				SUM(CASE 
						WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) = @Ngay_xem 
						THEN B.Thanh_tien 
						ELSE 0 
					END), 
				SUM(CASE 
						WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem 
						THEN B.Thanh_tien 
						ELSE 0 
					END),
				ROUND((SUM(CASE 
								WHEN CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Dau_thang AND @Ngay_xem 
								THEN B.Thanh_tien 
								ELSE 0 
							END)*100/@Tong_tien_thu), 2),
				@Chi,
				@Ngay_xem_in
			FROM ThuChi AS A LEFT OUTER JOIN PhieuThuChi AS B ON A.id = B.Ma_thu_chi
				LEFT OUTER JOIN ThanhVien AS C ON B.Ma_nhan_vien = C.id
			WHERE C.Ma_chi_nhanh = @Ma_chi_nhanh
				AND A.Ma_loai_thu_chi = 1
			GROUP BY A.Ten
		END
	END	
END

